﻿Imports Microsoft.Office.Interop.Excel

Class FLEnvioCartas

    Private tituloMensaje As String = "Cartas"
    Private listaCartasPE As List(Of CTEnvioCarta)
    Private lstCatalogoTVNG As New List(Of CTCatalogoGenerico)
    Private servicioGenerales As CSServiciosGenerales
    Private fechasInhabiles As List(Of CFechaNoLab) 
    Private servicioGeneral As CServiciosGenerales
    Private servicioCarta As CSGeneralEvioCartas
    Private fechaBSO As Date

    Public Sub New()
        Try
            Dim catalogo = New CTCatalogoGenerico()
            lstCatalogoTVNG = catalogo.ObtieneCatalogo(5)
            servicioGenerales = New CSServiciosGenerales()
            servicioGeneral = New CServiciosGenerales()
            servicioCarta = New CSGeneralEvioCartas()
            fechasInhabiles = servicioGeneral.ObtenerFechasNoLab(1)
            fechaBSO = servicioGenerales.ObtieneFechaParamBSO()
            InitializeComponent()
            CargaInicial()
        Catch ex As Exception
            MessageBox.Show("Error TI500. Error al cargar datos iniciales: " & ex.Message, tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Error)
        Finally
            Mouse.OverrideCursor = Nothing
        End Try
    End Sub

    Public Sub CargaInicial()
        Dim lstCatalogo As New List(Of CTCatalogoGenerico)
        Dim cat As CTCatalogoGenerico = New CTCatalogoGenerico()
        cat.IdCat = 0
        cat.Clave = 0
        cat.Valor = "TODOS"
        cat.Descripcion = "TODOS"
        lstCatalogo.Add(cat)
        For Each catalogo As CTCatalogoGenerico In lstCatalogoTVNG
            lstCatalogo.Add(catalogo)
        Next
        Me.cmbTipoSolicitud.ItemsSource = lstCatalogo
        Me.cmbTipoSolicitud.SelectedIndex = 0
        btnConsultar.IsEnabled = True        
        Me.dtpFechaSolicitud.Text = fechaBSO.ToString("dd/MM/yyyy")
        Me.btnEnviarCarta.IsEnabled = False
        Me.btnGenerarCarta.IsEnabled = False
        Me.grdCartas.ItemsSource = Nothing
        Me.chkSelecciona.IsEnabled = False
    End Sub

    Private Sub chkSelectAll_Checked(ByVal sender As Object, ByVal e As RoutedEventArgs)
        For Each envio As CTEnvioCarta In listaCartasPE
            envio.Seleccion = True
        Next
        Dim listCartas = New ListCollectionView(listaCartasPE)
        listCartas.GroupDescriptions.Add(New PropertyGroupDescription("Agrupador"))
        Me.grdCartas.ItemsSource = listCartas

    End Sub


    Private Sub chkSelectAll_Unchecked(ByVal sender As Object, ByVal e As RoutedEventArgs)
        For Each envio As CTEnvioCarta In listaCartasPE
            envio.Seleccion = False
        Next
        Dim listCartas = New ListCollectionView(listaCartasPE)
        listCartas.GroupDescriptions.Add(New PropertyGroupDescription("Agrupador"))
        Me.grdCartas.ItemsSource = listCartas
    End Sub

    Private Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnConsultar.Click
        Mouse.OverrideCursor = Cursors.Wait
        Try
            If ValidarConsulta() Then
                Dim catalogo As CTCatalogoGenerico = Me.cmbTipoSolicitud.SelectedItem
                Dim tvng As String
                tvng = catalogo.Clave.ToString()
                If catalogo.Clave = 0 Then
                    tvng = "%"
                End If
                listaCartasPE = servicioCarta.ObtieneCartasHistoricas(DateTime.Parse(Me.dtpFechaSolicitud.Text), tvng)
                
                Dim lstCartas = New ListCollectionView(listaCartasPE)
                lstCartas.GroupDescriptions.Add(New PropertyGroupDescription("Agrupador"))
                Me.grdCartas.ItemsSource = lstCartas
                If listaCartasPE.Count() = 0 Then
                    Me.chkSelecciona.IsEnabled = False
                    Me.btnEnviarCarta.IsEnabled = False
                    Me.btnGenerarCarta.IsEnabled = False
                    MessageBox.Show("No existe información", tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Information)
                Else
                    Me.btnEnviarCarta.IsEnabled = True
                    Me.btnGenerarCarta.IsEnabled = True
                    Me.chkSelecciona.IsEnabled = True
                End If
                

            End If
        Catch ex As Exception
            Mouse.OverrideCursor = Nothing
            MessageBox.Show("Error TI500. Error al realizar la consulta: " & ex.Message, tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Error)
        End Try
        Mouse.OverrideCursor = Nothing

    End Sub

    Private Function ValidarConsulta() As Boolean
        
        Dim FechaSol = DateTime.Parse(Me.dtpFechaSolicitud.Text)

        Dim fechaHabil As Boolean = ValidaFechaHabil(FechaSol)
        
        If Not fechaHabil Then
            MessageBox.Show("La fecha solicitud no puede ser un día inhabil.", tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Exclamation)
            Return False
        End If

        If DateTime.Parse(FechaSol.ToString("dd/MM/yyyy")) > DateTime.Parse(fechaBSO.ToString("dd/MM/yyyy")) Then
            MessageBox.Show("El filtro fecha solicitud no puede ser mayor a la fecha solicitud de BSO.", tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Exclamation)
            Return False
        End If

        If Me.cmbTipoSolicitud.SelectedIndex < 0 Then
            MessageBox.Show("Debe seleccionar un tipo de solicitud.", tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Exclamation)
            Return False
        End If

       
        Return True

        
    End Function

    Private Function ValidaFechaHabil(ByVal fecha As Date) As Boolean
        For Each fechain As CFechaNoLab In fechasInhabiles
            If fechain.Fechanolab.ToString("dd/MM/yyyy") = fecha.ToString("dd/MM/yyyy")
                Return False
            End If
        Next
        Return True
    End Function

    Private Sub btnLimpiar_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnLimpiar.Click
        CargaInicial()
    End Sub

    Private Sub btnGenerarCarta_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnGenerarCarta.Click
        Mouse.OverrideCursor = Cursors.Wait
        Dim idPSBit, idPEJBit, SecBit As Integer
        If ValidarSeleccion() Then
            Dim eventoBit = CBitacoraFRIDA.GenerarEvento(CBitacoraFRIDA.CveEventosBit.GENERAR_CARTA, CServiciosMacrosSyBaseDB.IDUSUARIO, "FLEnvioCartas.xaml -> btnGenerarCarta")
            Try
                Dim dialog As New Microsoft.Win32.SaveFileDialog()
                dialog.FileName = "Selecciona Directorio"
                dialog.ValidateNames = False
                dialog.CheckFileExists = False
                dialog.CheckPathExists = True
                If dialog.ShowDialog() Then
                    Dim ruta = dialog.FileName
                    Dim rutaAux() As String = ruta.Split("\")
                    ruta = ruta.Replace(rutaAux(rutaAux.Count - 1), "")
                    Dim lst = Me.grdCartas.ItemsSource
                    For Each enviocarta As CTEnvioCarta In lst
                        If enviocarta.Seleccion Then
                            idPSBit = enviocarta.IdPs
                            idPEJBit = enviocarta.IdPej
                            SecBit = enviocarta.SecDistrib
                            eventoBit.GuadarDetalle("idPS", idPSBit)
                            eventoBit.GuadarDetalle("idPEJ", idPEJBit)
                            eventoBit.GuadarDetalle("Sec", SecBit)
                            If enviocarta.IdTvNg < 5 Then
                                'Guber
                                ExportarCartaReportoExcel(enviocarta, ruta)
                            Else
                                'Ban
                                ExportarCartaBancarioExcel(enviocarta, ruta)
                            End If
                            
                        End If
                    Next
                    MessageBox.Show("Operación Exitosa" , tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Information)
                End If
             eventoBit.ActualizarFechaFinDelEvento()   
            Catch ex As Exception
                Dim errorBit = eventoBit.GuardarError("Error al generar Carta: " & ex.Message)
                errorBit.GuardarDetalle("idPS", idPSBit)
                errorBit.GuardarDetalle("idPEJ", idPEJBit)
                errorBit.GuardarDetalle("Sec", SecBit)
                eventoBit.ActualizarFechaFinDelEvento()
                Mouse.OverrideCursor = Nothing
                MessageBox.Show("Error TI500. Error al enviar cartas: " & ex.Message & " -Codigo:" & errorBit.IdError, tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Error)
            End Try
        End If
        Mouse.OverrideCursor = Nothing

    End Sub

    Private Sub btnEnviarCarta_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnEnviarCarta.Click
        Mouse.OverrideCursor = Cursors.Wait
        Dim idPSBit, idPEJBit, SecBit As Integer
        If ValidarSeleccion() Then
            Dim eventoBit = CBitacoraFRIDA.GenerarEvento(CBitacoraFRIDA.CveEventosBit.ENVIAR_CARTA, CServiciosMacrosSyBaseDB.IDUSUARIO, "FLEnvioCartas.xaml -> btnEnviarCarta")
            Try
                If MessageBox.Show ("¿Esta seguro que desea enviar las cartas?", tituloMensaje, MessageBoxButton.YesNo, MessageBoxImage.Question)  = MessageBoxResult.Yes Then
                    Dim errors As System.Text.StringBuilder = New System.Text.StringBuilder()
                    Dim ruta = servicioCarta.ObtieneRutaCarta()
                    Dim archivo As String
                    Dim lst = Me.grdCartas.ItemsSource
                    Dim estados As List(Of CTEstadoOperacionGral) 
                    For Each enviocarta As CTEnvioCarta In lst
                        If enviocarta.Seleccion Then
                            idPSBit = enviocarta.IdPs
                            idPEJBit = enviocarta.IdPej
                            SecBit = enviocarta.SecDistrib
                            eventoBit.GuadarDetalle("idPS", idPSBit)
                            eventoBit.GuadarDetalle("idPEJ", idPEJBit)
                            eventoBit.GuadarDetalle("Sec", SecBit)
                            estados = New List(Of CTEstadoOperacionGral) 
                            If enviocarta.IdTvNg < 5 Then
                                'Guber
                                archivo = ExportarCartaReportoExcel(enviocarta, ruta)
                            Else
                                'Ban
                                archivo = ExportarCartaBancarioExcel(enviocarta, ruta)
                            End If
                            'Enviar Carta
                            Dim archivoadjunto As List(Of String) = New List(Of String)
                            archivoadjunto.Add(archivo)
                            If Not servicioCarta.EnviarAutoCartaAIntermediarioEtAl(enviocarta.IdIntermediario, archivoadjunto, estados) Then
                                Dim errorBit = eventoBit.GuardarError("Error al enviar Carta: " & estados(0).Descripcion)
                                errorBit.GuardarDetalle("idPS", idPSBit)
                                errorBit.GuardarDetalle("idPEJ", idPEJBit)
                                errorBit.GuardarDetalle("Sec", SecBit)
                                Dim carta_nom() As String = archivo.Split("\")
                                errors.Append(carta_nom(carta_nom.Count - 1) & ": " & estados(0).Descripcion & " -Codigo:" & errorBit.IdError)
                            Else
                                servicioCarta.ActualizaCarta(enviocarta.IdPej, enviocarta.SecDistrib)
                            End If
                            'Eliminar Archivo
                            My.Computer.FileSystem.DeleteFile(archivo)
                        End If
                    Next
                    If (errors.ToString().Length > 0) Then
                        MessageBox.Show("Error al enviar las siguientes cartas: " &  vbCrLf & errors.ToString(), tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Error)
                    Else
                        MessageBox.Show("Operación Exitosa" , tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Information)
                    End If
                    
                End If
                
            eventoBit.ActualizarFechaFinDelEvento()    
            Catch ex As Exception
                Dim errorBit = eventoBit.GuardarError("Error al enviar Carta: " & ex.Message)
                errorBit.GuardarDetalle("idPS", idPSBit)
                errorBit.GuardarDetalle("idPEJ", idPEJBit)
                errorBit.GuardarDetalle("Sec", SecBit)
                eventoBit.ActualizarFechaFinDelEvento()
                Mouse.OverrideCursor = Nothing
                MessageBox.Show("Error TI500. Error al enviar cartas: " & ex.Message & " -Codigo:" & errorBit.IdError, tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Error)
            End Try
        End If
        Mouse.OverrideCursor = Nothing
    End Sub

    Private Function ValidarSeleccion() As Boolean
        Dim count As Integer = 0
        Dim lst = Me.grdCartas.ItemsSource
        For Each enviocarta As CTEnvioCarta In lst
            If enviocarta.Seleccion Then
                Return True
            End If
        Next
        MessageBox.Show ("Se debe seleccionar al menos un registro.", tituloMensaje, MessageBoxButton.OK, MessageBoxImage.Error) 
        Return False
    End Function

    Public Function ExportarCartaReportoExcel(ByRef carta As CTEnvioCarta, ByVal ruta As String) As String
        Dim fileName As String = servicioCarta.ObtieneNombreCarta()
        Dim fechaHoy = Date.Now()
        fileName = fileName & carta.Intermediario
        fileName = fileName & "_" & fechaHoy.ToString("yyyyMMdd") & "_" & fechaHoy.ToString("hhmmss")
        fileName = fileName & ".xls"
        fileName = ruta + fileName

        Dim filaInicio As Integer = 12

        'Get Data
        Dim detalle = servicioCarta.ObtieneDetalleCarta(carta.IdPej, carta.SecDistrib, carta.IdIntermediario)
        
        Dim aplicacion    As _Application
        Dim libro         As Workbook
        Dim hoja          As Worksheet

        Try
            aplicacion = CreateObject("Excel.Application")        
        Catch ex As Exception
            aplicacion = CreateObject("Excel.Application")
        End Try
        Try
            aplicacion.SheetsInNewWorkbook = 1
            aplicacion.DisplayAlerts = False
            'aplicacion.Visible = True
        Catch ex As Exception
            MessageBox.Show ("Error al intentar generar Excel",tituloMensaje,MessageBoxButton.OK,MessageBoxImage.Exclamation  ) 
            Return Nothing 
        End Try
        libro = aplicacion.Workbooks.Add
        hoja = libro.Sheets(1)

        'Formato Encabezado

        Dim path As String
        path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        path = path.Replace("\bin\Debug","")
        path = path.Replace("file:\","")
        hoja.Range("B1").ColumnWidth = 22.43
        hoja.Range("C1").ColumnWidth = 22.43
        hoja.Range("D1").ColumnWidth = 22.43
        hoja.Range("E1").ColumnWidth = 22.43


        aplicacion.ActiveSheet.Shapes.AddPicture(path & "\imagenes\LogoAsset.png", False, True, 67, 23, 231, 26)

        With hoja.Range("B5:E5").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B9:E9").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B10:E10").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B11:E11").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B12:E12").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(54, 96, 146)
        End With
        
        hoja.Range("B5:E" & (detalle.Count() + filaInicio + 2)).Font.Name = "Calibri"
        hoja.Range("B5:E" & (detalle.Count() + filaInicio + 2)).Font.Size = 12

        hoja.Range("C5:C8").Font.Bold = True
        hoja.Range("E5:E8").Font.Bold = True

        hoja.Range("B10:E10").Font.Bold = True

        'Datos y Labels Encabezado
        hoja.Range("B5").Value = "FONDOS COMPRA"
        hoja.Range("B6").Value = "INTERMEDIARIO"
        hoja.Range("B7").Value = "PLAZO"
        hoja.Range("B8").Value = "FOLIO DECALOG"


        hoja.Range("C5").Value = carta.TvNg
        hoja.Range("C6").Value = carta.Intermediario
        hoja.Range("C7").Value = carta.Plazo
        hoja.Range("C8").Value = carta.FolioDecalog

        hoja.Range("D5").Value = "OPERACIÓN"
        hoja.Range("D6").Value = "LIQUIDACIÓN"
        hoja.Range("D7").Value = "RENDIMIENTO"

        hoja.Range("E5").Value = carta.FechaSolicitud
        hoja.Range("E6").Value = carta.FechaLiquidacion
        hoja.Range("E7").Value = (carta.Tasa/100)

        hoja.Range("B10").Value = "SOC. DE INVERSIÓN"
        hoja.Range("C10").Value = "MONTO REAL"
        hoja.Range("D10").Value = "INDEVAL"
        hoja.Range("E10").Value = "CONTRATO"
        
        hoja.Range("B10:E10").HorizontalAlignment = Constants.xlCenter

        hoja.Range("E5:E6").NumberFormat = "dd-MMM-yy"
        hoja.Range("E7").NumberFormat = "0.0###%"
        hoja.Range("B12:B" & (detalle.Count() + filaInicio)).NumberFormat = "@"
        hoja.Range("C12:C" & (detalle.Count() + filaInicio)).NumberFormat = "##,##0.00"
        hoja.Range("D12:D" & (detalle.Count() + filaInicio)).NumberFormat = "@"
        hoja.Range("E12:E" & (detalle.Count() + filaInicio)).NumberFormat = "@"

        hoja.Range("D12:D" & (detalle.Count() + filaInicio)).HorizontalAlignment = Constants.xlCenter
        hoja.Range("E12:E" & (detalle.Count() + filaInicio)).HorizontalAlignment = Constants.xlRight
        
        Dim index As Integer
        For index = 0 To (detalle.Count() - 1)
            hoja.Cells(index + filaInicio, 2).value = detalle(index).Fondo
            hoja.Cells(index + filaInicio, 3).value = detalle(index).Cantidad
            hoja.Cells(index + filaInicio, 4).value = detalle(index).CtoIndeval
            hoja.Cells(index + filaInicio, 5).value = detalle(index).CtoIntermediario
        Next

        'Final
        With hoja.Range("B" & (detalle.Count() + filaInicio + 1) &":E" & (detalle.Count() + filaInicio + 1)).Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(54, 96, 146)
        End With
        
        hoja.Range("B" & (detalle.Count() + filaInicio + 2)).Value = "TOTAL:"
        hoja.Range("C" & (detalle.Count() + filaInicio + 2)).Formula = "=SUM(C" & filaInicio & ":C" & (detalle.Count() + filaInicio - 1) & ")"
        hoja.Range("C" & (detalle.Count() + filaInicio + 2)).NumberFormat = "##,##0.00"

        libro.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Type.Missing, _
                     Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

        ReleaseObject(hoja)
        libro.Close(False, Type.Missing, Type.Missing)
        ReleaseObject(libro)
        aplicacion.Quit()
        ReleaseObject(aplicacion)
        GC.Collect()
        Return fileName
    End Function

    Public Function ExportarCartaBancarioExcel(ByRef carta As CTEnvioCarta, ByVal ruta As String) As String

        Dim vn As Integer = 1
        
        Dim tasa As Double = carta.Tasa / 100
        Dim plazo As Integer = carta.Plazo
        Dim precio As Double = vn / (1+tasa/360*plazo)
        Dim aux = precio.ToString("#.0000000000")
        aux = aux.Substring(0, aux.Length - 2)
        precio = Convert.ToDouble(aux)


        Dim fileName As String = servicioCarta.ObtieneNombreCarta()
        Dim fechaHoy = Date.Now()
        fileName = fileName & carta.Intermediario
        fileName = fileName & "_" & fechaHoy.ToString("yyyyMMdd") & "_" & fechaHoy.ToString("hhmmss")
        fileName = fileName & ".xls"
        fileName = ruta + fileName

        Dim filaInicio As Integer = 12

        'Get Data
        Dim detalle = servicioCarta.ObtieneDetalleCarta(carta.IdPej, carta.SecDistrib, carta.IdIntermediario)
        
        Dim aplicacion    As _Application
        Dim libro         As Workbook
        Dim hoja          As Worksheet

        Try
            aplicacion = CreateObject("Excel.Application")        
        Catch ex As Exception
            aplicacion = CreateObject("Excel.Application")
        End Try
        Try
            aplicacion.SheetsInNewWorkbook = 1
            aplicacion.DisplayAlerts = False
            'aplicacion.Visible = True
        Catch ex As Exception
            MessageBox.Show ("Error al intentar generar Excel",tituloMensaje,MessageBoxButton.OK,MessageBoxImage.Exclamation  ) 
            Return Nothing 
        End Try
        libro = aplicacion.Workbooks.Add
        hoja = libro.Sheets(1)

        'Formato Encabezado

        Dim path As String
        path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        path = path.Replace("\bin\Debug","")
        path = path.Replace("file:\","")
        hoja.Range("B1").ColumnWidth = 22.43
        hoja.Range("C1").ColumnWidth = 22.43
        hoja.Range("D1").ColumnWidth = 22.43
        hoja.Range("E1").ColumnWidth = 22.43
        hoja.Range("F1").ColumnWidth = 22.43

        aplicacion.ActiveSheet.Shapes.AddPicture(path & "\imagenes\LogoAsset.png", False, True, 67, 23, 231, 26)

        With hoja.Range("B5:F5").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B9:F9").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B10:F10").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B11:F11").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(22, 54, 92)
        End With

        With hoja.Range("B12:F12").Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(54, 96, 146)
        End With
        
        hoja.Range("B5:F" & (detalle.Count() + filaInicio + 2)).Font.Name = "Calibri"
        hoja.Range("B5:F" & (detalle.Count() + filaInicio + 2)).Font.Size = 12

        hoja.Range("C5:C8").Font.Bold = True
        hoja.Range("F5:F8").Font.Bold = True

        hoja.Range("B10:F10").Font.Bold = True

        'Datos y Labels Encabezado
        hoja.Range("B5").Value = "FONDOS COMPRA"
        hoja.Range("B6").Value = "INTERMEDIARIO"
        hoja.Range("B7").Value = "PLAZO"
        hoja.Range("B8").Value = "FOLIO DECALOG"


        hoja.Range("C5").Value = carta.TvNg
        hoja.Range("C6").Value = carta.Intermediario
        hoja.Range("C7").Value = carta.Plazo
        hoja.Range("C8").Value = carta.FolioDecalog

        hoja.Range("E5").Value = "OPERACIÓN"
        hoja.Range("E6").Value = "LIQUIDACIÓN"
        hoja.Range("E7").Value = "RENDIMIENTO"
        hoja.Range("E8").Value = "PRECIO"

        hoja.Range("F5").Value = carta.FechaSolicitud
        hoja.Range("F6").Value = carta.FechaLiquidacion
        hoja.Range("F7").Value = (carta.Tasa/100)
        hoja.Range("F8").Value = precio

        hoja.Range("B10").Value = "SOC. DE INVERSIÓN"
        hoja.Range("C10").Value = "MONTO REAL"
        hoja.Range("D10").Value = "TITULOS"
        hoja.Range("E10").Value = "INDEVAL"
        hoja.Range("F10").Value = "CONTRATO"

        hoja.Range("B10:F10").HorizontalAlignment = Constants.xlCenter


        hoja.Range("F5:F6").NumberFormat = "dd-MMM-yy"
        hoja.Range("F7").NumberFormat = "0.0###%"
        hoja.Range("F8").NumberFormat = "##,##0.00#######"
        hoja.Range("B12:B" & (detalle.Count() + filaInicio)).NumberFormat = "@"
        hoja.Range("C12:C" & (detalle.Count() + filaInicio)).NumberFormat = "##,##0.00"
        hoja.Range("D12:D" & (detalle.Count() + filaInicio)).NumberFormat = "##,##0"
        hoja.Range("E12:E" & (detalle.Count() + filaInicio)).NumberFormat = "@"
        hoja.Range("F12:F" & (detalle.Count() + filaInicio)).NumberFormat = "@"

        hoja.Range("E12:E" & (detalle.Count() + filaInicio)).HorizontalAlignment = Constants.xlCenter
        hoja.Range("F12:F" & (detalle.Count() + filaInicio)).HorizontalAlignment = Constants.xlRight
        
        Dim titulos As Double
        Dim monto As Double
        Dim index As Integer
        For index = 0 To (detalle.Count() - 1)
            hoja.Cells(index + filaInicio, 2).value = detalle(index).Fondo
            'hoja.Cells(index + filaInicio, 3).value = detalle(index).Cantidad

            titulos = 0
            aux = (detalle(index).Cantidad / precio).ToString("#.0")
            aux = aux.Substring(0, aux.Length - 2)
            titulos = Convert.ToDouble(aux)
            monto = titulos * precio
            monto = Math.Round(monto, 2)
            hoja.Cells(index + filaInicio, 4).value = titulos'Titulos
            hoja.Cells(index + filaInicio, 3).value = monto

            hoja.Cells(index + filaInicio, 5).value = detalle(index).CtoIndeval
            hoja.Cells(index + filaInicio, 6).value = detalle(index).CtoIntermediario
        Next

        'Final
        With hoja.Range("B" & (detalle.Count() + filaInicio + 1) &":F" & (detalle.Count() + filaInicio + 1)).Borders(XlBordersIndex.xlEdgeTop)
            .LineStyle = XlLineStyle.xlContinuous
            .Weight = XlBorderWeight.xlMedium
            .Color = RGB(54, 96, 146)
        End With
        
        hoja.Range("B" & (detalle.Count() + filaInicio + 2)).Value = "TOTAL:"
        hoja.Range("C" & (detalle.Count() + filaInicio + 2)).Formula = "=SUM(C" & filaInicio & ":C" & (detalle.Count() + filaInicio - 1) & ")"
        hoja.Range("C" & (detalle.Count() + filaInicio + 2)).NumberFormat = "##,##0.00"

        hoja.Range("D" & (detalle.Count() + filaInicio + 2)).Formula = "=SUM(D" & filaInicio & ":D" & (detalle.Count() + filaInicio - 1) & ")"
        hoja.Range("D" & (detalle.Count() + filaInicio + 2)).NumberFormat = "##,##0"

        libro.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Type.Missing, _
                     Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

        ReleaseObject(hoja)
        libro.Close(False, Type.Missing, Type.Missing)
        ReleaseObject(libro)
        aplicacion.Quit()
        ReleaseObject(aplicacion)
        GC.Collect()
        Return fileName
    End Function


    Public Sub ReleaseObject(ByVal o As Object)
        Try
            While(System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
            End While
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

End Class
